
package FileSystemView;

import java.io.File;
import java.io.FileInputStream;

import java.io.IOException;
import java.io.InputStream;


import jxl.Cell;
import jxl.CellType;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import FileSystemView.ReadFromFile;

public class ExcelOperate {

	protected int index_Sheet = 0;



	/**
	 * Read data from a excel file
	 * 
	 * @param sourceFile
	 */
	public static void readerExcel(File sourceFile) {
		jxl.Workbook readbook = null;
		try {
			// InputStream stream = new FileInputStream(sourceFilePath);
			// readbook = jxl.Workbook.getWorkbook(stream);

			readbook = jxl.Workbook.getWorkbook(sourceFile);

			Sheet sheet = readbook.getSheet(0);
			Cell cell = null;

			int nombreColumns = sheet.getColumns();
			int nombreRows = sheet.getRows();
			String text = null;

			for (int i = 0; i < nombreRows; i++) {
				for (int j = 0; j < nombreColumns; j++) {
					// attention: The first parameter is column,the second
					// parameter is row.
					cell = sheet.getCell(j, i);
					text = cell.getContents();
					if (cell.getType() == CellType.LABEL) {
						// LabelCell labelCell = (LabelCell)cell;
						// text = labelCell.getString();
						text += "LABEL";
					} else if (cell.getType() == CellType.NUMBER)
						text += " number";
					else if (cell.getType() == CellType.DATE)
						text += " date";
					System.out.println("00==>" + text);
				}

			}
			// stream.close();
			readbook.close();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * Read data from a excel file
	 * 
	 * @param excelFileName
	 */
	public static void readExcel(String excelFileName) {
		Workbook rwb = null;
		try {
			InputStream stream = new FileInputStream(excelFileName);
			rwb = Workbook.getWorkbook(stream);
			Sheet sheet = rwb.getSheet(0);
			Cell cell = null;
			int columns = sheet.getColumns();
			int rows = sheet.getRows();
			for (int i = 0; i < rows; i++)
				for (int j = 0; j < columns; j++) {
					// attention: The first parameter is column,the second
					// parameter is row.
					cell = sheet.getCell(j, i);
					String str00 = cell.getContents();
					if (cell.getType() == CellType.LABEL) {
						str00 += " LAEBL";
					} else if (cell.getType() == CellType.NUMBER)
						str00 += " number";
					else if (cell.getType() == CellType.DATE)
						str00 += " date";
					System.out.println("00==>" + str00);
				}
			stream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} finally {
			rwb.close();
		}
	}


	/**
	 * 
	 * @param book
	 * @param fileTxt
	 * @param sheetName
	 * @throws IOException
	 * @throws Exception
	 */
	public void writerFileTxtExcel(WritableWorkbook book, ReadFromFile fileTxt,
			String sheetName) throws IOException, Exception {
		
		try {
			
			WritableSheet sheet = book.createSheet(sheetName, index_Sheet++);
			ExcelOperate.writerFileTxtToSheet(sheet,fileTxt);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Modify Excel
	 * 
	 * @param file1
	 * @param file2
	 */
	public static void modifyExcel(File originalFile, File targetFile,
			ReadFromFile fileTxt, String sheetName, int SheetNumber) {
		try {
			Workbook original_book = Workbook.getWorkbook(originalFile);
			WritableWorkbook target_book = Workbook.createWorkbook(targetFile,
					original_book);// copy

			// ------------------------------------------

			int nbSheet = target_book.getNumberOfSheets();
			WritableSheet target_sheet = target_book.createSheet(sheetName,nbSheet);// next sheet
			writerFileTxtToSheet(target_sheet,fileTxt);
			// ------------------------------------------
			target_book.write();
			target_book.close();
			original_book.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * writer File To Sheet
	 * @param sheet
	 * @param fileTxt
	 * @throws IOException
	 * @throws Exception
	 */

	public static void writerFileTxtToSheet(WritableSheet sheet, ReadFromFile fileTxt)
								throws IOException, Exception {

		try {

			// -----------------------------------------------------------------

			int columns = 0;
			int rows = 0;

			// read ".txt" file information (buffFileInfo) and write in excel
			// file
			for (int row = 0; row < fileTxt.getBuffFileInfo().size(); row++) {
				String s = (String) fileTxt.getBuffFileInfo().get(row).toString();
				// System.out.println(s);

				// writer in cell of sheet
				if (rows != 2) {
					Label myLabel = new Label(columns, row, s);
					sheet.addCell(myLabel);
				} else {
					Label myLabel = new Label(columns + 1, row - 1, s);
					sheet.addCell(myLabel);
				}

				rows = row; // return line 2

			}

			int column = 0;
			//int count = 0;
			// read ".txt" file data(buffFileData) and write in excel file
			rows = rows + 2; // line 4
			for (Object s : fileTxt.getBuffFileData()) {

				Label myLabel = new Label(column++, rows, s.toString());
				sheet.addCell(myLabel);

				if (s.toString().contains("\n")) {
					rows++;
					column = 0;
				}

			}

			// -------------------------------------------------------------

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
